package com.le.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.le.bean.Board;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface BoardDao extends BaseMapper {

    @Select("select * from tbl_board where boardid = #{boardid}")
    public Board selectBoardById(Integer boardid);

    @Select("select a.boardid,boardname,parentid,total, topicid,title,modifytime,uname\n" +
            "   from\n" +
            "       (\n" +
            "       select tbl_board.boardid,boardname,parentid , count( topicid ) as total\n" +
            "       from tbl_board\n" +
            "       left join tbl_topic\n" +
            "       on tbl_board.boardid=tbl_topic.boardid\n" +
            "       group by tbl_board.boardid,boardname,parentid\n" +
            "       ) a\n" +
            "       left join\n" +
            "       (\n" +
            "       select topicid,title,a.modifytime,uname,a.boardid\n" +
            "       from\n" +
            "       (\tselect topicid, title, modifytime, uname, boardid\n" +
            "       from tbl_topic\n" +
            "       left join tbl_user\n" +
            "       on tbl_topic.uid=tbl_user.uid\n" +
            "       ) a,\n" +
            "       (\n" +
            "       select boardid,max(modifytime) as modifytime\n" +
            "       from tbl_topic\n" +
            "       group by boardid\n" +
            "       ) b\n" +
            "       where  a.boardid=b.boardid and a.modifytime=b.modifytime\n" +
            "       )b\n" +
            "   on a.boardid=b.boardid")
    List<Board> selectAllBoard();
}
